package com.tianqu.tms.dao;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.tianqu.tms.core.base.Logger;
import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.dto.MarketCarVendorMonthCountDto;



public class MarketCarVendorCountDaoImpl extends TmsDao implements MarketCarVendorCountDao  {
	private static Logger logger = Logger.getLogger(MarketCarVendorCountDaoImpl.class);//log
	private static DecimalFormat format1 = new DecimalFormat("###,###");
	private static DecimalFormat format2 = new DecimalFormat("###,###.##");
	
	public MarketCarVendorCountDaoImpl() throws TmsDaoException{
		super();
	}

	@Override
	public List<MarketCarVendorMonthCountDto> findYearlyReport() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<MarketCarVendorMonthCountDto> list = new ArrayList<MarketCarVendorMonthCountDto>();
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append(" sum(onlineCount) onlineSum,");
		sql.append(" avg(onlineCount) onlineAvg,");
		sql.append(" sum(newCount) newSum,");
		sql.append(" avg(newCount) newAvg,");
		sql.append(" sum(offlineCount) offlineSum, ");
		sql.append(" avg(offlineCount) offlineAvg, ");
		sql.append(" countY ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_MARKET_CARVENDOR_MONTH_COUNT);
		sql.append(" group BY countY ");
		sql.append(" order by countY ");
		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while(rs.next()){
				MarketCarVendorMonthCountDto dto = new MarketCarVendorMonthCountDto();
				dto.setOnlineSum(rs.getString("onlineSum"));
				dto.setOnlineAvg(rs.getString("onlineAvg"));
				dto.setNewSum(rs.getString("newSum"));
				dto.setNewAvg(rs.getString("newAvg"));
				dto.setOfflineSum(rs.getString("offlineSum"));
				dto.setOfflineAvg(rs.getString("offlineAvg"));
				dto.setCountY(rs.getString("countY"));
				dto.setName(dto.getCountY()+"年");
				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}	
		
		//
		BigDecimal totalOnlineSum = BigDecimal.ZERO;
		BigDecimal totalOnlineAvg = BigDecimal.ZERO;
		BigDecimal totalNewSum = BigDecimal.ZERO;
		BigDecimal totalNewAvg = BigDecimal.ZERO;
		BigDecimal totalOfflineSum = BigDecimal.ZERO;
		BigDecimal totalOfflineAvg = BigDecimal.ZERO;
		for (MarketCarVendorMonthCountDto dto : list) {
			totalOnlineSum = totalOnlineSum.add(new BigDecimal(dto.getOnlineSum()));
			totalOnlineAvg = totalOnlineAvg.add(new BigDecimal(dto.getOnlineAvg()));
			totalNewSum = totalNewSum.add(new BigDecimal(dto.getNewSum()));
			totalNewAvg = totalNewAvg.add(new BigDecimal(dto.getNewAvg()));
			totalOfflineSum = totalOfflineSum.add(new BigDecimal(dto.getOfflineSum()));
			totalOfflineAvg = totalOfflineAvg.add(new BigDecimal(dto.getOfflineAvg()));
		}
		MarketCarVendorMonthCountDto first = new MarketCarVendorMonthCountDto();
		first.setName("总数");
		if(!list.isEmpty()) {
			first.setOnlineSum(totalOnlineSum.toPlainString());
			first.setOnlineAvg(totalOnlineAvg.divide(new BigDecimal(list.size()), 2, BigDecimal.ROUND_HALF_UP).toPlainString());
			first.setNewSum(totalNewSum.toPlainString());
			first.setNewAvg(totalNewAvg.divide(new BigDecimal(list.size()), 2, BigDecimal.ROUND_HALF_UP).toPlainString());
			first.setOfflineSum(totalOfflineSum.toPlainString());
			first.setOfflineAvg(totalOfflineAvg.divide(new BigDecimal(list.size()), 2, BigDecimal.ROUND_HALF_UP).toPlainString());
		}
		list.add(0, first);
		
		for (MarketCarVendorMonthCountDto dto : list) {
			dto.setOnlineSum(f1(dto.getOnlineSum()));
			dto.setNewSum(f1(dto.getNewSum()));
			dto.setOfflineSum(f1(dto.getOfflineSum()));
			dto.setOnlineAvg(f2(dto.getOnlineAvg()));
			dto.setNewAvg(f2(dto.getNewAvg()));
			dto.setOfflineAvg(f2(dto.getOfflineAvg()));
		}
		//
		return list;
	}

	@Override
	public List<MarketCarVendorMonthCountDto> findMonthlyReport(String year) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<MarketCarVendorMonthCountDto> list = new ArrayList<MarketCarVendorMonthCountDto>();
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append(" sum(onlineCount) onlineSum,");
		sql.append(" avg(onlineCount) onlineAvg,");
		sql.append(" sum(newCount) newSum,");
		sql.append(" avg(newCount) newAvg,");
		sql.append(" sum(offlineCount) offlineSum, ");
		sql.append(" avg(offlineCount) offlineAvg, ");
		sql.append(" countY,countM ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_MARKET_CARVENDOR_MONTH_COUNT);
		sql.append(" where countY="+year);
		sql.append(" group BY countM ");
		sql.append(" order by countM ");
		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while(rs.next()){
				MarketCarVendorMonthCountDto dto = new MarketCarVendorMonthCountDto();
				dto.setOnlineSum(rs.getString("onlineSum"));
				dto.setOnlineAvg(rs.getString("onlineAvg"));
				dto.setNewSum(rs.getString("newSum"));
				dto.setNewAvg(rs.getString("newAvg"));
				dto.setOfflineSum(rs.getString("offlineSum"));
				dto.setOfflineAvg(rs.getString("offlineAvg"));
				dto.setCountY(rs.getString("countY"));
				dto.setCountM(rs.getString("countM"));
				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}	
		
		//
		HashMap<Integer, MarketCarVendorMonthCountDto> map = new HashMap<Integer, MarketCarVendorMonthCountDto>();
		for (MarketCarVendorMonthCountDto dto : list) map.put(Integer.parseInt(dto.getCountM()), dto);
		
		List<MarketCarVendorMonthCountDto> carList = new ArrayList<MarketCarVendorMonthCountDto>();
		for(int i =1; i<=12;i++) {
			MarketCarVendorMonthCountDto dto = map.get(i);
			if(dto == null) {
				dto = new MarketCarVendorMonthCountDto();
				dto.setCountY(year);
				dto.setCountM(String.valueOf(i));
				dto.setName(String.valueOf(i) + "月");
			}
			carList.add(dto);
		}
		
		//
		BigDecimal totalOnlineSum = BigDecimal.ZERO;
		BigDecimal totalOnlineAvg = BigDecimal.ZERO;
		BigDecimal totalNewSum = BigDecimal.ZERO;
		BigDecimal totalNewAvg = BigDecimal.ZERO;
		BigDecimal totalOfflineSum = BigDecimal.ZERO;
		BigDecimal totalOfflineAvg = BigDecimal.ZERO;
		for (MarketCarVendorMonthCountDto dto : carList) {
			totalOnlineSum = totalOnlineSum.add(new BigDecimal(dto.getOnlineSum()));
			totalOnlineAvg = totalOnlineAvg.add(new BigDecimal(dto.getOnlineAvg()));
			totalNewSum = totalNewSum.add(new BigDecimal(dto.getNewSum()));
			totalNewAvg = totalNewAvg.add(new BigDecimal(dto.getNewAvg()));
			totalOfflineSum = totalOfflineSum.add(new BigDecimal(dto.getOfflineSum()));
			totalOfflineAvg = totalOfflineAvg.add(new BigDecimal(dto.getOfflineAvg()));
		}
		MarketCarVendorMonthCountDto first = new MarketCarVendorMonthCountDto();
		first.setName("总数");
		if(!list.isEmpty()) {
			first.setOnlineSum(totalOnlineSum.toPlainString());
			first.setOnlineAvg(totalOnlineAvg.divide(new BigDecimal(list.size()), 2, BigDecimal.ROUND_HALF_UP).toPlainString());
			first.setNewSum(totalNewSum.toPlainString());
			first.setNewAvg(totalNewAvg.divide(new BigDecimal(list.size()), 2, BigDecimal.ROUND_HALF_UP).toPlainString());
			first.setOfflineSum(totalOfflineSum.toPlainString());
			first.setOfflineAvg(totalOfflineAvg.divide(new BigDecimal(list.size()), 2, BigDecimal.ROUND_HALF_UP).toPlainString());
		}
		carList.add(0, first);
		
		//
		for (MarketCarVendorMonthCountDto dto : carList) {
			dto.setOnlineSum(f1(dto.getOnlineSum()));
			dto.setNewSum(f1(dto.getNewSum()));
			dto.setOfflineSum(f1(dto.getOfflineSum()));
			dto.setOnlineAvg(f2(dto.getOnlineAvg()));
			dto.setNewAvg(f2(dto.getNewAvg()));
			dto.setOfflineAvg(f2(dto.getOfflineAvg()));
		}

		//
		return carList;
	}

	@Override
	public List<MarketCarVendorMonthCountDto> findDailyReport(String year, String month) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<MarketCarVendorMonthCountDto> list = new ArrayList<MarketCarVendorMonthCountDto>();
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append(" sum(onlineCount) onlineSum,");
		sql.append(" avg(onlineCount) onlineAvg,");
		sql.append(" sum(newCount) newSum,");
		sql.append(" avg(newCount) newAvg,");
		sql.append(" sum(offlineCount) offlineSum, ");
		sql.append(" avg(offlineCount) offlineAvg, ");
		sql.append(" countY, countM, date_format(countDate, '%d') countDate ");
		sql.append(" FROM ");
		sql.append(TmsTableNames.TMS_MARKET_CARVENDOR_DAY_COUNT);
		sql.append(" where countY="+year);
		sql.append(" and countM="+month);
		sql.append(" group BY countDate ");
		sql.append(" order by countDate ");
		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while(rs.next()){
				MarketCarVendorMonthCountDto dto = new MarketCarVendorMonthCountDto();
				dto.setOnlineSum(rs.getString("onlineSum"));
				dto.setOnlineAvg(rs.getString("onlineAvg"));
				dto.setNewSum(rs.getString("newSum"));
				dto.setNewAvg(rs.getString("newAvg"));
				dto.setOfflineSum(rs.getString("offlineSum"));
				dto.setOfflineAvg(rs.getString("offlineAvg"));
				dto.setCountD(rs.getString("countDate"));
				dto.setCountM(rs.getString("countM"));
				dto.setCountY(rs.getString("countY"));
				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}	
		
		//
		HashMap<Integer, MarketCarVendorMonthCountDto> map = new HashMap<Integer, MarketCarVendorMonthCountDto>();
		for (MarketCarVendorMonthCountDto dto : list) map.put(Integer.parseInt(dto.getCountD()), dto);
		Calendar c = Calendar.getInstance();
		c.set(Integer.parseInt(year), Integer.parseInt(month), 1);
		int lastDay = c.getActualMaximum(Calendar.DAY_OF_MONTH);
		List<MarketCarVendorMonthCountDto> carList = new ArrayList<MarketCarVendorMonthCountDto>();
		for(int i = 1; i <=lastDay; i++) {
			MarketCarVendorMonthCountDto dto = map.get(i);
			if(dto == null) {
				dto = new MarketCarVendorMonthCountDto();
				dto.setCountY(year);
				dto.setCountY(month);
				dto.setCountD(String.valueOf(i));
				dto.setName(String.valueOf(i)+"日");
			}
			carList.add(dto);
		}
		
		//
		BigDecimal totalOnlineSum = BigDecimal.ZERO;
		BigDecimal totalOnlineAvg = BigDecimal.ZERO;
		BigDecimal totalNewSum = BigDecimal.ZERO;
		BigDecimal totalNewAvg = BigDecimal.ZERO;
		BigDecimal totalOfflineSum = BigDecimal.ZERO;
		BigDecimal totalOfflineAvg = BigDecimal.ZERO;
		for (MarketCarVendorMonthCountDto dto : list) {
			totalOnlineSum = totalOnlineSum.add(new BigDecimal(dto.getOnlineSum()));
			totalOnlineAvg = totalOnlineAvg.add(new BigDecimal(dto.getOnlineAvg()));
			totalNewSum = totalNewSum.add(new BigDecimal(dto.getNewSum()));
			totalNewAvg = totalNewAvg.add(new BigDecimal(dto.getNewAvg()));
			totalOfflineSum = totalOfflineSum.add(new BigDecimal(dto.getOfflineSum()));
			totalOfflineAvg = totalOfflineAvg.add(new BigDecimal(dto.getOfflineAvg()));
		}
		MarketCarVendorMonthCountDto first = new MarketCarVendorMonthCountDto();
		first.setName("总数");
		if(!list.isEmpty()) {
			first.setOnlineSum(totalOnlineSum.toPlainString());
			first.setOnlineAvg(totalOnlineAvg.divide(new BigDecimal(list.size()), 2, BigDecimal.ROUND_HALF_UP).toPlainString());
			first.setNewSum(totalNewSum.toPlainString());
			first.setNewAvg(totalNewAvg.divide(new BigDecimal(list.size()), 2, BigDecimal.ROUND_HALF_UP).toPlainString());
			first.setOfflineSum(totalOfflineSum.toPlainString());
			first.setOfflineAvg(totalOfflineAvg.divide(new BigDecimal(list.size()), 2, BigDecimal.ROUND_HALF_UP).toPlainString());
		}
		carList.add(0, first);
		
		//
		for (MarketCarVendorMonthCountDto dto : carList) {
			dto.setOnlineSum(f1(dto.getOnlineSum()));
			dto.setNewSum(f1(dto.getNewSum()));
			dto.setOfflineSum(f1(dto.getOfflineSum()));
			dto.setOnlineAvg(f2(dto.getOnlineAvg()));
			dto.setNewAvg(f2(dto.getNewAvg()));
			dto.setOfflineAvg(f2(dto.getOfflineAvg()));
		}

		//
		return carList;
	}
	
	/**
	 * 
	 */
	private static String f1(String str) {
		if(StringUtils.isNotEmpty(str)) {
			return format1.format(new BigDecimal(str));
		}
		return "";
	}
	
	private  static String f2(String str) {
		if(StringUtils.isNotEmpty(str)) {
			return format2.format(new BigDecimal(str));
		}
		return "";
	}
}